package jdbc;

import java.sql.*;

public class JDBCDemo {

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //创建表
    public static void createTable(String sql) throws  SQLException {

        String url="jdbc:mysql://localhost:3306/mydb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
        Connection connection = DriverManager.getConnection(url,"root","xieming11");
        System.out.println("与数据库建立连接！！");
        Statement statement = connection.createStatement();
        statement.execute(sql);
        connection.close();
    }
    public static int updateValue(String sql)throws  SQLException{

        //执行DML操作:INSERT. UPDATE, DELETE
        String url="jdbc:mysql://localhost:3306/mydb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
        Connection connection = DriverManager.getConnection(url,"root","xieming11");
        System.out.println("与数据库建立连接！！");
        Statement statement = connection.createStatement();
        int num = statement.executeUpdate(sql);
        connection.close();
        return num;
    }
    public static void main(String[] args) {
        /*
        1、加载驱动
        2、通过DriverManager与DBMS建立连接
        3、通过连接对象创建语句对象statement
        4、通过语句对象执行相应的sql语句给数据库
        5、获得执行结果
         */
        try {
            //SQL：注入功击密码：a' OR '1'='1'
            //执行DDL语句,主要用于创建表，删表等删除
            /* String sql = "CREATE TABLE userInfo(" +
                    " id INT PRIMARY KEY AUTO_INCREMENT,"+
                    " username VARCHAR(30)," +
                    " password VARCHAR(30)," +
                    " nickname VARCHAR(30)," +
                    " age INT(3)" +")";
            createTable(sql);*/
            String sql = "insert into userinfo(username,password,nickname,age)"+
                         " values('谢小明','xieming11','xiaoming',41)";
            if(updateValue(sql)>0)
                System.out.println("更新成功");
            else
                System.out.println("更新失败");

            Statement statement = DBUtil.getConnection().createStatement();
            sql="select * from userinfo";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getInt(1)+"\t"+
                        resultSet.getString(2)+"\t"+
                        resultSet.getString(3));
            }

            //解决注入功击
            //预编译SQL语句的写法，将原来需要拼接的值，先用？代替
            sql = "select id,username,password,nickname,age "+
                    " from userinfo "+
                    "WHERE username=? and password=?";
            PreparedStatement preparedStatement = DBUtil.getConnection().prepareStatement(sql);
            preparedStatement.setString(1,"xieming");
            preparedStatement.setString(2,"123456");

            ResultSet rs = preparedStatement.executeQuery();
            if(rs.next()){
                System.out.println("用户名与密码正确");
            }else
                System.out.println("不正确");

        }catch ( SQLException  throwables)
        {
            throwables.printStackTrace();
        }
    }
}
